package org.rujue.common.utils.excel;
 
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import java.io.*;
import java.util.*;
 
/**
 * Excel 读取excel中的数据
 *
 * @author Administrator
 * @date 2022年03月11日
 */
public class ReadExcelComponent {
    public static void main(String[] args) {
        List<Map<String, Object>> allItems = ReadExcelComponent.readExcelDatas("G:\\test.xlsx");
        for (Map<String, Object> map : allItems) {
            System.out.println(map);
        }
    }


    private static String getFileExtname(String filename) {
        return filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();
    }
 
    /**
     * 读取某个sheet中的数据
     *
     * @param path     文件路径
     * @param sheetNum sheet号
     */
    public static Map<String, Object> readExcelData(String path, int sheetNum) {
        List<Map<String, Object>> dataList = readExcelDatas(path);
        if (dataList.size() == 0) {
            return null;
        }
        return dataList.stream().filter(map -> MapUtils.getIntValue(map, "sheetIndex", 0) == sheetNum).findAny().orElse(null);
    }
 
    /**
     * 读取Excel中所有数据 多个sheet
     *
     * @param path 文件路径
     */
    public static List<Map<String, Object>> readExcelDatas(String path) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        if (path == null || path.length() == 0) {
            return dataList;
        }
        File file = new File(path);
        String fileName = file.getName();
        String fileSuffix = getFileExtname(fileName);
        Workbook workbook = null;
        try {
            if ("xls".equals(fileSuffix)) {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            }
            if ("xlsx".equals(fileSuffix)) {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return dataList;
        }
 
        List<Sheet> workbookSheets = getWorkbookSheetList(workbook);
        for (int sheetIndex = 0; sheetIndex < workbookSheets.size(); sheetIndex++) {
            Sheet sheet = workbookSheets.get(sheetIndex);
            List<Row> sheetRowList = getSheetRowList(sheet);
            String sheetName = sheet.getSheetName();
            Map<String, Object> sheetDataList = new LinkedHashMap<>();
            List<List<String>> rows = new ArrayList<>();
            sheetRowList.forEach(row -> {
                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                List<String> cellDataList = new ArrayList<>();
                for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    cellDataList.add(getCellValue(cell));
                }
                rows.add(cellDataList);
            });
            sheetDataList.put("sheetName", sheetName);
            sheetDataList.put("sheetIndex", sheetIndex + 1);
            sheetDataList.put("sheetRows", rows);
            dataList.add(sheetDataList);
        }
        return dataList;
    }
 
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return "";
        }
        try {
            CellType cellType = cell.getCellType();
 
            /*Numeric 数字类型*/
            if (cellType == CellType.NUMERIC) {
                cellValue = String.valueOf(cell.getNumericCellValue());
            }
 
            /*字符串*/
            if (cellType == CellType.STRING) {
                cellValue = cell.getStringCellValue();
            }
            /*公式 Formula*/
            if (cellType == CellType.FORMULA) {
                cellValue = cell.getCellFormula();
            }
            /*空行*/
            if (cellType == CellType.BLANK) {
                cellValue = "";
            }
            /*布尔类型*/
            if (cellType == CellType.BOOLEAN) {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            }
            /*Error Cell type */
            if (cellType == CellType.ERROR) {
                cellValue = String.valueOf(cell.getErrorCellValue());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
 
        return cellValue;
 
    }
 
    /*获取sheet 中行数据*/
    private static List<Row> getSheetRowList(Sheet sheet) {
        List<Row> items = new ArrayList<>();
        if (sheet == null) {
            return items;
        }
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int rowIndex = 0; rowIndex < physicalNumberOfRows; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            items.add(row);
        }
        return items;
    }
 
    /*获取excel 中 sheet*/
    private static List<Sheet> getWorkbookSheetList(Workbook workbook) {
        List<Sheet> items = new ArrayList<>();
        if (workbook == null) {
            return items;
        }
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            items.add(sheet);
        }
        return items;
    }
 
}